﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PJSearchService
    {
        public List<ProjectMain> GetMain(PJSearch PJSearch)
        {
            string sql = "select * from PJMains";
            sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=PJMains.WorkParentId";
            sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " where Statues=@Statues";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Statues", "1"));
            if(PJSearch.Id2==-1)
            {
                sql += " and PJId like '%'+@PJId+'%'";
                param.Add(new SqlParameter("@PJId", PJSearch.PJId));
            }
            else
            {
                sql += " and Id>=@Id1 and Id<=@Id2";
                param.Add(new SqlParameter("@Id1", PJSearch.Id1));
                param.Add(new SqlParameter("@Id2", PJSearch.Id2));
            }
            if(!string.IsNullOrEmpty(PJSearch.PJName))
            {
                sql += " and PJName=@PJName";
                param.Add(new SqlParameter("@PJName", PJSearch.PJName));
            }
            if(!string.IsNullOrEmpty(PJSearch.CreaterName))
            {
                sql += " and DisplayName=@DisplayName";
                param.Add(new SqlParameter("@DisplayName", PJSearch.CreaterName));
            }
            if(PJSearch.PJType!="全部")
            {
                sql += " and PJType=@PJType";
                param.Add(new SqlParameter("@PJType", PJSearch.PJType));
            }
            if(PJSearch.MoldType!="全部")
            {
                sql += " and MoldType like '%'+@MoldType+'%'";
                param.Add(new SqlParameter("@MoldType", PJSearch.MoldType));
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            List<ProjectMain> list = new List<ProjectMain>();
            while(objReader.Read())
            {
                list.Add(new ProjectMain()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    PJName = objReader["PJName"].ToString(),
                    Mold = objReader["Mold"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    MaktxName = objReader["MaktxName"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    BusinessPlate = objReader["BusinessPlate"].ToString(),
                    PM = objReader["PM"].ToString(),
                    StartDate = Convert.ToDateTime(objReader["StartDate"]),
                    PJType = objReader["PJType"].ToString(),
                    MoldType = objReader["MoldType"].ToString(),
                    DisplayName = objReader["DisplayName"].ToString(),
                    T1EndDate = Convert.ToDateTime(objReader["T1EndDate"]),
                    T2EndDate = Convert.ToDateTime(objReader["T2EndDate"]),
                    T3EndDate = Convert.ToDateTime(objReader["T3EndDate"]),
                    T4EndDate = Convert.ToDateTime(objReader["T4EndDate"]),
                    T5EndDate = Convert.ToDateTime(objReader["T5EndDate"]),
                    T1RealEndDate = objReader["T1RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T1RealEndDate"]),
                    T2RealEndDate = objReader["T2RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T2RealEndDate"]),
                    T3RealEndDate = objReader["T3RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T3RealEndDate"]),
                    T4RealEndDate = objReader["T4RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T4RealEndDate"]),
                    T5RealEndDate = objReader["T5RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T5RealEndDate"])
                });
            }
            objReader.Close();
            return list;
        }
        public int GetId(string pjId)
        {
            string sql = "select Id from PJMains where PJId=@PJId";
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, new SqlParameter[] { new SqlParameter("@PJId", pjId) }));
        }

        public List<PJLog> GetLog(string pjId)
        {
            string sql = "select * from PJLogs where PJId=@PJId";
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@PJId", pjId) });
            List<PJLog> list = new List<PJLog>();
            while(objReader.Read())
            {
                list.Add(new PJLog()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    PJId = objReader["PJId"].ToString(),
                    Stage = objReader["Stage"].ToString(),
                    Person = objReader["Person"].ToString(),
                    CompleteDate = Convert.ToDateTime(objReader["CompleteDate"]),
                    LogContent = objReader["LogContent"].ToString(),
                    Statues = objReader["Statues"].ToString(),
                    Creater = objReader["Creater"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        public ProjectMain GetMain(string pjId)
        {
            string sql = "select * from PJMains";
            //sql += " inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=PJMains.WorkParentId";
            //sql += " inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName";
            sql += " where Statues=@Statues and PJId=@PJId";

            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Statues", "1"),
                new SqlParameter("@PJId",pjId)
            };
            
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            ProjectMain PJMain = null;
            if (objReader.Read())
            {
                PJMain = new ProjectMain()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    PJId = objReader["PJId"].ToString(),
                    PJName = objReader["PJName"].ToString(),
                    Mold = objReader["Mold"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    MaktxName = objReader["MaktxName"].ToString(),
                    Customer = objReader["Customer"].ToString(),
                    BusinessPlate = objReader["BusinessPlate"].ToString(),
                    PM = objReader["PM"].ToString(),
                    StartDate = Convert.ToDateTime(objReader["StartDate"]),
                    PJType = objReader["PJType"].ToString(),
                    MoldType = objReader["MoldType"].ToString()
                    //DisplayName = objReader["DisplayName"].ToString(),
                    //T1EndDate = Convert.ToDateTime(objReader["T1EndDate"]),
                    //T2EndDate = Convert.ToDateTime(objReader["T2EndDate"]),
                    //T3EndDate = Convert.ToDateTime(objReader["T3EndDate"]),
                    //T4EndDate = Convert.ToDateTime(objReader["T4EndDate"]),
                    //T5EndDate = Convert.ToDateTime(objReader["T5EndDate"]),
                    //T1RealEndDate = objReader["T1RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T1RealEndDate"]),
                    //T2RealEndDate = objReader["T2RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T2RealEndDate"]),
                    //T3RealEndDate = objReader["T3RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T3RealEndDate"]),
                    //T4RealEndDate = objReader["T4RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T4RealEndDate"]),
                    //T5RealEndDate = objReader["T5RealEndDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["T5RealEndDate"])
                };
            }
            objReader.Close();
            return PJMain;
        }
    }
}
